using Core.SimpleTemp.Application.YH.Dto;
using Core.SimpleTemp.Common;
using Core.SimpleTemp.Common.PagingQuery;
using Core.SimpleTemp.Entitys;
using Core.SimpleTemp.Repositories;
using Core.Toolkit.Common;
using Core.Toolkit.Core;
using Core.Toolkit.Core.Api;
using Core.Toolkit.Core.Application;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Core.SimpleTemp.Application
{
    /// <summary>
    /// 隐患_隐患台账
    /// </summary>
    [AutoDi()]
    public class YH_StandingBookChartService : BaseAppService<YH_StandingBookDto, YH_StandingBook, YH_StandingBookRepository>
    {
        readonly ExcelHelp _excelHelp;
        readonly Toolkit.Core.Repositories.SQLHelper _helper;
        public YH_StandingBookChartService(YH_StandingBookRepository repository, ExcelHelp excelHelp, Toolkit.Core.Repositories.SQLHelper helper) : base(repository)
        {
            _excelHelp = excelHelp;
            _helper = helper;
        }

        //隐患类别饼状图统计
        public async Task<List<YH_StandingBookRiskTypeChartDto>> GetRiskTypeChart(string DiscoveryTimeStart, string DiscoveryTimeEnd, string RiskLevel, string Department)
        {
            List<YH_StandingBookRiskTypeChartDto> list = new List<YH_StandingBookRiskTypeChartDto>();
            string where = " where yh_sb.IsDeleted='0' ";
            if (DiscoveryTimeStart != "" && DiscoveryTimeEnd != "")
            {
                where = where + " and  yh_sb.DiscoveryTime BETWEEN '" + DiscoveryTimeStart + " 00:00:00.000' and '" + DiscoveryTimeEnd + " 23:59:59.000' ";
            }
            else
            {
                if (DiscoveryTimeStart == null || DiscoveryTimeStart == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime>='" + DiscoveryTimeStart + " 00:00:00.000' ";
                }
                if (DiscoveryTimeEnd == null || DiscoveryTimeEnd == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime<='" + DiscoveryTimeEnd + " 23:59:59.000' ";
                }
            }
            if (RiskLevel == null || RiskLevel == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskLevel='" + RiskLevel + "' ";
            }
            if (Department == null || Department == "")
            {

            }
            else
            {
                where = where + " and yh_sb.Department='" + Department + "' ";
            }
            System.Data.DataTable dt = _helper.ExecuteDataTable("select yh_t.[Type] as name,count(*) as [value] from YH_StandingBook as yh_sb left join YH_Type as yh_t on yh_sb.RiskType=yh_t.Id " + where + " Group by yh_t.Type ", null);
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                YH_StandingBookRiskTypeChartDto dto = new YH_StandingBookRiskTypeChartDto();
                dto.name = dt.Rows[z]["name"].ToString();
                dto.value = Convert.ToInt32(dt.Rows[z]["value"].ToString());
                list.Add(dto);
            }
            return list;
        }


        //隐患级别饼状图统计
        public async Task<List<YH_StandingBookRiskTypeChartDto>> GetRiskLevelChart(string DiscoveryTimeStart, string DiscoveryTimeEnd, string RiskType, string Department)
        {
            List<YH_StandingBookRiskTypeChartDto> list = new List<YH_StandingBookRiskTypeChartDto>();
            string where = " where yh_sb.IsDeleted='0' ";
            if (DiscoveryTimeStart != "" && DiscoveryTimeEnd != "")
            {
                where = where + " and  yh_sb.DiscoveryTime BETWEEN '" + DiscoveryTimeStart + " 00:00:00.000' and '" + DiscoveryTimeEnd + " 23:59:59.000' ";
            }
            else
            {
                if (DiscoveryTimeStart == null || DiscoveryTimeStart == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime>='" + DiscoveryTimeStart + " 00:00:00.000' ";
                }
                if (DiscoveryTimeEnd == null || DiscoveryTimeEnd == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime<='" + DiscoveryTimeEnd + " 23:59:59.000' ";
                }
            }
            if (RiskType == null || RiskType == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskType='" + RiskType + "' ";
            }
            if (Department == null || Department == "")
            {

            }
            else
            {
                where = where + " and yh_sb.Department='" + Department + "' ";
            }
            System.Data.DataTable dt = _helper.ExecuteDataTable("select yh_g.[Level] as name,count(*) as [value] from YH_StandingBook as yh_sb left join YH_Grade as yh_g on yh_sb.RiskLevel=yh_g.Id " + where + " Group by yh_g.Level ", null);
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                YH_StandingBookRiskTypeChartDto dto = new YH_StandingBookRiskTypeChartDto();
                dto.name = dt.Rows[z]["name"].ToString();
                dto.value = Convert.ToInt32(dt.Rows[z]["value"].ToString());
                list.Add(dto);
            }
            return list;
        }

        //隐患工厂饼状图统计
        public async Task<List<YH_StandingBookRiskTypeChartDto>> GetRiskFactoryChart(string DiscoveryTimeStart, string DiscoveryTimeEnd, string RiskType, string RiskLevel)
        {
            List<YH_StandingBookRiskTypeChartDto> list = new List<YH_StandingBookRiskTypeChartDto>();
            string where = " where yh_sb.IsDeleted='0' and sys_f.FactoryType='0' ";
            if (DiscoveryTimeStart != "" && DiscoveryTimeEnd != "")
            {
                where = where + " and  yh_sb.DiscoveryTime BETWEEN '" + DiscoveryTimeStart + " 00:00:00.000' and '" + DiscoveryTimeEnd + " 23:59:59.000' ";
            }
            else
            {
                if (DiscoveryTimeStart == null || DiscoveryTimeStart == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime>='" + DiscoveryTimeStart + " 00:00:00.000' ";
                }
                if (DiscoveryTimeEnd == null || DiscoveryTimeEnd == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime<='" + DiscoveryTimeEnd + " 23:59:59.000' ";
                }
            }
            if (RiskType == null || RiskType == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskType='" + RiskType + "' ";
            }
            if (RiskLevel == null || RiskLevel == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskLevel='" + RiskLevel + "' ";
            }
            System.Data.DataTable dt = _helper.ExecuteDataTable("select sys_f.[FactoryName] as name,count(*) as [value] from YH_StandingBook as yh_sb left join SysFactory as sys_f on yh_sb.CreateFactory=sys_f.Id " + where + " Group by sys_f.FactoryName ", null);
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                YH_StandingBookRiskTypeChartDto dto = new YH_StandingBookRiskTypeChartDto();
                dto.name = dt.Rows[z]["name"].ToString();
                dto.value = Convert.ToInt32(dt.Rows[z]["value"].ToString());
                list.Add(dto);
            }
            return list;
        }

        //隐患部门饼状图统计
        public async Task<List<YH_StandingBookRiskTypeChartDto>> GetRiskDepartmentChart(string DiscoveryTimeStart, string DiscoveryTimeEnd, string RiskType, string RiskLevel)
        {
            List<YH_StandingBookRiskTypeChartDto> list = new List<YH_StandingBookRiskTypeChartDto>();
            string where = " where yh_sb.IsDeleted='0' ";
            if (DiscoveryTimeStart != "" && DiscoveryTimeEnd != "")
            {
                where = where + " and  yh_sb.DiscoveryTime BETWEEN '" + DiscoveryTimeStart + " 00:00:00.000' and '" + DiscoveryTimeEnd + " 23:59:59.000' ";
            }
            else
            {
                if (DiscoveryTimeStart == null || DiscoveryTimeStart == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime>='" + DiscoveryTimeStart + " 00:00:00.000' ";
                }
                if (DiscoveryTimeEnd == null || DiscoveryTimeEnd == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime<='" + DiscoveryTimeEnd + " 23:59:59.000' ";
                }
            }
            if (RiskType == null || RiskType == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskType='" + RiskType + "' ";
            }
            if (RiskLevel == null || RiskLevel == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskLevel='" + RiskLevel + "' ";
            }
            System.Data.DataTable dt = _helper.ExecuteDataTable("select sys_d.[Name] as name,count(*) as [value] from YH_StandingBook as yh_sb left join SysDepartment as sys_d on yh_sb.Department=sys_d.Id " + where + " Group by sys_d.Name ", null);
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                YH_StandingBookRiskTypeChartDto dto = new YH_StandingBookRiskTypeChartDto();
                dto.name = dt.Rows[z]["name"].ToString();
                dto.value = Convert.ToInt32(dt.Rows[z]["value"].ToString());
                list.Add(dto);
            }
            return list;
        }

        //隐患趋势折线图统计  天
        public async Task<List<YH_StandingBookRiskTrendLineChartDto>> GetRiskTrendLineChart(string Level,string DiscoveryTimeStart, string DiscoveryTimeEnd, string RiskType, string RiskLevel, string Department)
        {
            List<YH_StandingBookRiskTrendLineChartDto> list = new List<YH_StandingBookRiskTrendLineChartDto>();
            string where = " where yh_sb.IsDeleted='0' ";
            if (Level == null || Level == "")
            {

            }
            else
            {
                where = where + " and yh_sb.RiskLevel in(select Id from YH_Grade where [Level]='"+ Level + "') ";
            }
            if (DiscoveryTimeStart != "" && DiscoveryTimeEnd != "")
            {
                where = where + " and  CONVERT(varchar(100),DiscoveryTime, 23) BETWEEN '" + DiscoveryTimeStart + "' and '" + DiscoveryTimeEnd + "' ";
            }
            else
            {
                if (DiscoveryTimeStart == null || DiscoveryTimeStart == "")
                {

                }
                else
                {
                    where = where + " and  CONVERT(varchar(100),DiscoveryTime, 23)>='" + DiscoveryTimeStart + "' ";
                }
                if (DiscoveryTimeEnd == null || DiscoveryTimeEnd == "")
                {

                }
                else
                {
                    where = where + " and  CONVERT(varchar(100),DiscoveryTime, 23)<='" + DiscoveryTimeEnd + "' ";
                }
            }
            if (RiskType == null || RiskType == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskType='" + RiskType + "' ";
            }
            if (RiskLevel == null || RiskLevel == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskLevel='" + RiskLevel + "' ";
            }
            if (Department == null || Department == "")
            {

            }
            else
            {
                where = where + " and yh_sb.Department='" + Department + "' ";
            }
            System.Data.DataTable dt = _helper.ExecuteDataTable("select CONVERT(varchar(100),DiscoveryTime, 23) as DiscoveryDay,count(*) as [value] from YH_StandingBook as yh_sb " + where + "  Group by CONVERT(varchar(100),DiscoveryTime, 23) ", null);
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                YH_StandingBookRiskTrendLineChartDto dto = new YH_StandingBookRiskTrendLineChartDto();
                dto.time = dt.Rows[z]["DiscoveryDay"].ToString();
                dto.value = Convert.ToInt32(dt.Rows[z]["value"].ToString());
                list.Add(dto);
            }
            return list;
        }


        //按承包商统计
        public async Task<IPageModel<YH_StandingBookContractorDto>> GetContractorStatistics(PagingModel<YH_StandingBookContractorDto> pagingQueryModel)
        {
            string DiscoveryTimeStart = pagingQueryModel.Model.TimeArrayBegin;
            string DiscoveryTimeEnd = pagingQueryModel.Model.TimeArrayEnd;
            string Contractor = pagingQueryModel.Model.Contractor.ToString();
            List<YH_StandingBookContractorDto> list = new List<YH_StandingBookContractorDto>();
            string where = " where yh_sb.IsDeleted='0' and yh_sb.HaveContractor='1' ";

            if (DiscoveryTimeStart != "" && DiscoveryTimeEnd != "")
            {
                where = where + " and  yh_sb.DiscoveryTime BETWEEN '" + DiscoveryTimeStart + " 00:00:00.000' and '" + DiscoveryTimeEnd + " 23:59:59.000' ";
            }
            else
            {
                if (DiscoveryTimeStart == null || DiscoveryTimeStart == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime>='" + DiscoveryTimeStart + " 00:00:00.000' ";
                }
                if (DiscoveryTimeEnd == null || DiscoveryTimeEnd == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.DiscoveryTime<='" + DiscoveryTimeEnd + " 23:59:59.000' ";
                }
            }
            if (Contractor == null || Contractor == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.Contractor='" + Contractor + "' ";
            }
            
            System.Data.DataTable dt = _helper.ExecuteDataTable("select yh_sb.Contractor as Contractor,cbs_l.[ContractorName] as ContractorName,count(*) as [RiskCount] from YH_StandingBook as yh_sb left join CBS_List as cbs_l on yh_sb.Contractor=cbs_l.Id" + where + " Group by yh_sb.Contractor,cbs_l.ContractorName", null);
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                YH_StandingBookContractorDto dto = new YH_StandingBookContractorDto();
                dto.Contractor = Guid.Parse(dt.Rows[z]["Contractor"].ToString());
                dto.ContractorName = dt.Rows[z]["ContractorName"].ToString();
                dto.RiskCount = Convert.ToInt32(dt.Rows[z]["RiskCount"].ToString());
                list.Add(dto);
            }
            PageModel<YH_StandingBookContractorDto> pageModelDto = new PageModel<YH_StandingBookContractorDto>()
            {
                PageData = list,
                RowCount = list.Count
            };

            return pageModelDto;
        }
        //隐患整改率统计
        public async Task<IPageModel<YH_StandingBookHiddenTroubleRectificationDto>> GetHiddenTroubleRectification(PagingModel<YH_StandingBookHiddenTroubleRectificationDto> pagingQueryModel)
        {
            string DiscoveryTimeStart = pagingQueryModel.Model.PlanRectificationTimeStart;
            string DiscoveryTimeEnd = pagingQueryModel.Model.PlanRectificationTimeEnd;
            List<YH_StandingBookHiddenTroubleRectificationDto> list = new List<YH_StandingBookHiddenTroubleRectificationDto>();
            string where = " where yh_sb.IsDeleted='0' ";
            string where2 = "where yh_sb.IsDeleted='0' and yh_sb.ActualTime is not null";

            if (DiscoveryTimeStart != "" && DiscoveryTimeEnd != "")
            {
                where = where + " and  yh_sb.PlanTime BETWEEN '" + DiscoveryTimeStart + " 00:00:00.000' and '" + DiscoveryTimeEnd + " 23:59:59.000' ";
                where2 = where2 + " and  yh_sb.PlanTime BETWEEN '" + DiscoveryTimeStart + " 00:00:00.000' and '" + DiscoveryTimeEnd + " 23:59:59.000' ";
            }
            else
            {
                if (DiscoveryTimeStart == null || DiscoveryTimeStart == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.PlanTime>='" + DiscoveryTimeStart + " 00:00:00.000' ";
                    where2 = where2 + " and  yh_sb.PlanTime>='" + DiscoveryTimeStart + " 00:00:00.000' ";

                }
                if (DiscoveryTimeEnd == null || DiscoveryTimeEnd == "")
                {

                }
                else
                {
                    where = where + " and  yh_sb.PlanTime<='" + DiscoveryTimeEnd + " 23:59:59.000' ";
                    where2 = where2 + " and  yh_sb.PlanTime<='" + DiscoveryTimeEnd + " 23:59:59.000' ";
                }
            }

            System.Data.DataTable dt = _helper.ExecuteDataTable("select isnull(yh_p.PlanDay,'') as PlanDay,yh_p.PlanRectificationNum,isnull(yh_a.ActualCompletionNum,0) as ActualCompletionNum,isnull(round(yh_a.ActualCompletionNum/yh_p.PlanRectificationNum*100,2),0) as RectificationRate from (select CONVERT(varchar(7),PlanTime, 120) as PlanDay,count(*) as PlanRectificationNum from YH_StandingBook as yh_sb "+ where + " Group by CONVERT(varchar(7),PlanTime, 120)) as yh_p  left join(select CONVERT(varchar(7),PlanTime, 120) as PlanDay,count(*) as ActualCompletionNum from YH_StandingBook as yh_sb " + where2 + " Group by CONVERT(varchar(7),PlanTime, 120)) as yh_a on yh_p.PlanDay=yh_a.PlanDay order by yh_a.PlanDay", null);
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                YH_StandingBookHiddenTroubleRectificationDto dto = new YH_StandingBookHiddenTroubleRectificationDto();
                dto.PlanDay = dt.Rows[z]["PlanDay"].ToString();
                dto.PlanRectificationNum = Convert.ToInt32(dt.Rows[z]["PlanRectificationNum"].ToString());
                dto.ActualCompletionNum = Convert.ToInt32(dt.Rows[z]["ActualCompletionNum"].ToString());
                dto.RectificationRate = dt.Rows[z]["RectificationRate"].ToString()+"%";
                list.Add(dto);
            }
            PageModel<YH_StandingBookHiddenTroubleRectificationDto> pageModelDto = new PageModel<YH_StandingBookHiddenTroubleRectificationDto>()
            {
                PageData = list,
                RowCount = list.Count
            };

            return pageModelDto;
        }
        //隐患整改率统计 导出
        public async Task<string>
           ExportHiddenTroubleRectification(PagingModel<YH_StandingBookHiddenTroubleRectificationDto>
               pagingQueryModel)
        {
           
            var DataList = await this.GetHiddenTroubleRectification(pagingQueryModel);
            var excelData = DataList.PageData.Select(model => new
            {
                model.PlanDay,
                PlanDay_title = "计划整改时间",
                model.PlanRectificationNum,
                PlanRectificationNum_title = "计划整改数",
                model.ActualCompletionNum,
                ActualCompletionNum_title = "实际完成数",
                model.RectificationRate,
                RectificationRate_title = "整改率",
            }).ToList();

            var fileName = "隐患整改率统计" + TempDirHelp.GetTimeStampFielName(".xlsx");
            return _excelHelp.Create(excelData, fileName);


            //return _excelHelp.Create(excelData);
        }



        //重复隐患统计
        public async Task<List<YH_StandingBookRiskTypeChartDto>> GetRepeatRiskPieChart(string Topn, string DiscoveryTimeStart, string DiscoveryTimeEnd, string RiskType, string RiskLevel, string Department)
        {
            List<YH_StandingBookRiskTypeChartDto> list = new List<YH_StandingBookRiskTypeChartDto>();
            string where = " where yh_sb.IsDeleted='0' ";
            string topStr = "";
            string orderStr = " ORDER BY [value] desc ";
            if (Topn == null || Topn == "")
            {
                
            }
            else
            {
                topStr = topStr + " top " + Topn + "  ";
                
            }
            if (DiscoveryTimeStart != "" && DiscoveryTimeEnd != "") {
                where = where + " and  CONVERT(varchar(100),DiscoveryTime, 23) BETWEEN '" + DiscoveryTimeStart + "' and '" + DiscoveryTimeEnd + "' ";
            }
            else
            {
                if (DiscoveryTimeStart == null || DiscoveryTimeStart == "")
                {
                    
                }
                else
                {
                    where = where + " and  CONVERT(varchar(100),DiscoveryTime, 23)>='" + DiscoveryTimeStart + "' ";
                }
                if (DiscoveryTimeEnd == null || DiscoveryTimeEnd == "")
                {

                }
                else
                {
                    where = where + " and  CONVERT(varchar(100),DiscoveryTime, 23)<='" + DiscoveryTimeEnd + "' ";
                }
            }
            if (RiskType == null || RiskType == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskType='" + RiskType + "' ";
            }
            if (RiskLevel == null || RiskLevel == "")
            {

            }
            else
            {
                where = where + "  and yh_sb.RiskLevel='" + RiskLevel + "' ";
            }
            if (Department == null || Department == "")
            {

            }
            else
            {
                where = where + " and yh_sb.Department='" + Department + "' ";
            }
            System.Data.DataTable dt = _helper.ExecuteDataTable("SELECT "+ topStr + " StandardContent as name,COUNT(StandardContent) as [value] FROM  YH_StandingBook as yh_sb  " + where + " and StandardContent IN (SELECT StandardContent FROM YH_StandingBook as yh_sb " + where + " GROUP BY StandardContent HAVING COUNT(StandardContent)>1 ) GROUP BY StandardContent " + orderStr, null);
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                YH_StandingBookRiskTypeChartDto dto = new YH_StandingBookRiskTypeChartDto();
                dto.name = dt.Rows[z]["name"].ToString();
                dto.value = Convert.ToInt32(dt.Rows[z]["value"].ToString());
                list.Add(dto);
            }
            return list;
        }



    }

}







